import os
import json
import pandas as pd

PROJECT_DIR = os.path.dirname(__file__)
json_file = os.path.join(PROJECT_DIR, "1.json")
excel_file = os.path.join(PROJECT_DIR, "商户信息.xlsx")

with open(json_file, "r", encoding="utf-8") as file:
    data = json.load(file)

# 提取参数
gs_list = data["Result"]
my_gs_list = []
for gs in gs_list:
    gs_dict = {}
    # 商户信息
    gs_dict["shtydm"] = gs["CreditCode"]
    gs_dict["shmc"] = gs["Name"].replace("<em>", "").replace("</em>", "")

    # 人员信息
    infos = gs["CountInfo"]
    for info in infos:
        if info["k"] == "10":
            operType = json.loads(info["v"]).get("OperType")
            if operType == 1:
                gs_dict["OperType"] = "法人"
            if operType == 3:
                gs_dict["OperType"] = "负责人"
            if operType == 4:
                gs_dict["OperType"] = "经营者"
            if operType == 5:
                gs_dict["OperType"] = "投资人"
    gs_dict["cust_name"] = gs["OperName"]

    # 企业类型
    bq = gs["TagsInfoV2"]
    gs_dict["shlx"] = "null"
    if bq:
        bq_str = str(bq)
        if "小微企业" in bq_str:
            gs_dict["shlx"] = "小微企业"
        if "个体工商户" in bq_str:
            gs_dict["shlx"] = "个体工商户"
    my_gs_list.append(gs_dict)

new_df = pd.DataFrame(my_gs_list)

# 个体工商户更新
gt_df = new_df[new_df["shlx"] == "个体工商户"]


# 小微企业更新
xw_df = new_df[new_df["shlx"] == "小微企业"]
xw_df = xw_df[xw_df["OperType"].isin(["法人", "投资人"])]


if not os.path.exists(excel_file):
    with pd.ExcelWriter(excel_file) as writer:
        gt_df.to_excel(writer, sheet_name="个体工商户", index=False)
        xw_df.to_excel(writer, sheet_name="小微企业", index=False)

else:
    all_sheet_pds = pd.read_excel(excel_file, sheet_name=["个体工商户", "小微企业"])
    with pd.ExcelWriter(excel_file) as writer:
        for name, sheet in all_sheet_pds.items():
            if name == "个体工商户":
                sheet = pd.concat([sheet, gt_df], ignore_index=True)
                sheet = sheet.drop_duplicates()
                print("个体工商户:{}".format(sheet.shape[0]))
                sheet.to_excel(writer, sheet_name="个体工商户", index=False)
            if name == "小微企业":
                sheet = pd.concat([sheet, xw_df], ignore_index=True)
                sheet = sheet.drop_duplicates()
                print("小微企业:{}".format(sheet.shape[0]))
                sheet.to_excel(writer, sheet_name="小微企业", index=False)
